﻿#include "shujujieshou.h"
#include "ui_shujujieshou.h"
#include <QFileDialog>
#include <QMessageBox>
#include <QDebug>
#include <QIODevice>
#include <QTextEdit>
#include <QFile>
#include <QTextStream>
#include <QTextCodec>
#include <QSqlQuery>
#include <QTableWidget>
#include <QDomDocument>
shujujieshou::shujujieshou(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::shujujieshou)
{
    QTextCodec::setCodecForTr(QTextCodec::codecForName("UTF8"));
    QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF8"));
    QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF8"));
    ui->setupUi(this);
    ui->table->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->table->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->choose->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->choose->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->sameplace->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->sameplace->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->sameschooledu->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->sameschooledu->setEditTriggers(QAbstractItemView::NoEditTriggers);

    ui->sameworkedu->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->sameworkedu->setEditTriggers(QAbstractItemView::NoEditTriggers);



    ui->sameplacenum->setAttribute(Qt::WA_TransparentForMouseEvents, true);
    ui->sameworkedunum->setAttribute(Qt::WA_TransparentForMouseEvents, true);
    ui->sameschooledunum->setAttribute(Qt::WA_TransparentForMouseEvents, true);

    ui->scrollAreaWidgetContents->setAttribute(Qt::WA_TransparentForMouseEvents, true);
    //不点击数据无法点击按钮；
    ui->pushButton_2->setEnabled(false);
    ui->pushButton_4->setEnabled(false);
    ui->pushButton_3->setEnabled(false);
 ui->pushButton_6->setEnabled(false);
    //时间选择
    //    ui->dateEdit->setDisplayFormat("yyyy-MM-dd");//设置输出格式 ; 开始时间格式为2000-01-01 样式
    //    ui->dateEdit->setCalendarPopup(true);
    //UserImpowerDate = ui->dateEdit->text();//获取授权时间
    //connect(ui->pushButton_2,SIGNAL(clicked(bool)),this,SLOT(pressBtn()));//选择干部按钮连接判断
}

shujujieshou::~shujujieshou()
{
    delete ui;
}

void shujujieshou::on_pushButton_clicked()//导入文件
{
    QSqlQuery query;
    QSqlQuery changepolitic;//转换政治面貌代码
    QSqlQuery changeunit;//转换单位代码到单位名称
    QSqlQuery changejiguan;
    QString fileName = QFileDialog :: getOpenFileName(this, NULL,NULL, "*.xml");//打开xml文件
    qDebug()<<fileName;
    QFile file(fileName);

    //    //--打开文件成功
    //    if (file.open(QFile ::ReadOnly | QFile ::Text))
    //    {
    //        //qDebug()<<"1";
    //        QTextStream textStream(&file);
    //        while (!textStream.atEnd())
    //        {
    //            //---QtextEdit按行显示文件内容
    //            ui->showText->append(textStream.readLine());
    //        }
    //    }
    //    else	//---打开文件失败
    //    {
    //        /*
    //            information函数参数说明：
    //            函数原型
    //            QMessageBox::information(
    //            QWidget * parent,
    //            const QString & title,					//--- 标题
    //            const QString & text,					//---显示内容
    //            StandardButtons buttons = Ok,	//---OK按钮
    //            StandardButton defaultButton = NoButton)
    //            [static]
    //            */
    //        QMessageBox ::information(NULL, NULL, "open file error");
    //    }
    //打开文件成功后的操作
    //    QDomElement docElem = qDomDoc.documentElement();
    //    QDomNode nodeswitch=docElem.elementsByTagName("item");//红色的为标签名
    //    QDomElement elemnodeswitch=nodeswitch.toElement();
    //    string lie=qPrintable(elemnodeswitch.attribute("lie"));
    //    if(lie=="id")
    //    {
    //    qDebug()<<"succes";
    //    }
    QDomDocument docXML;
    if (!file.open(QIODevice::ReadOnly))
    {
        return ;
    }
    if (!docXML.setContent(&file))
    {
        file.close();
        return;
    }
    file.close();
    QDomElement xmlRoot = docXML.documentElement(); //返回根节点，table节点
    QDomNode domNode = xmlRoot.firstChild(); //获得第一个子节点,hang节点
    //    QString Name = xmlRoot.tagName();
    //    qDebug()<<Name;
    while (!domNode.isNull())  //如果节点不空
    {
        //        QString tagName = domNode.toElement().tagName();  //节点元素名称，hang节点
        //        qDebug()<<tagName;
        int row=ui->table->rowCount();//当前table行数
        ui->table->insertRow(row);//增加一行table
        QDomElement domElement = domNode.toElement(); //转换为元素，注意元素和节点是两个数据结构，其实差不多
        //        QDomNodeList list = domElement.childNodes();
        //        for (int i = 0; i<list.count(); i++) //遍历子元素，count和size都可以用,可用于标签数计数
        //        {
        QDomNode node = domElement.firstChild();
        while (!node.isNull()) {
            if (node.toElement().tagName() == "name") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,0,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "birth") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,1,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "id") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,2,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "sex") {
                QString no=node.firstChild().toText().data();
                if(no=="1"){
                    no="男";
                }else if(no=="0")
                {
                    no="女";
                }
                ui->table->setItem(row,3,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "nuit") {
                QString no=node.firstChild().toText().data();
                changeunit.exec(QString("select unitname from unit where unitid ='%1'").arg(no));
                while(changeunit.next()){
                    no=changeunit.value(0).toString();
                }
                ui->table->setItem(row,4,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "zhiwu") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,5,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "minzu") {
                QString no=node.firstChild().toText().data();
                query.exec(QString("select Nation from NationCode where NationID ='%1'").arg(no));
                while(query.next())
                {
                    QString Nation=query.value(0).toString();
                    ui->table->setItem(row,6,new QTableWidgetItem(Nation));
                }
            }
            else if (node.toElement().tagName() == "rudang") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,7,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "joinwork") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,8,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "health") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,9,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "jiguan") {
                QString no=node.firstChild().toText().data();
                changejiguan.exec(QString("select AdminArea from AdminAreaCode where AdminAreaID='%1'").arg(no));
                while(changejiguan.next()){
                    no=changejiguan.value(0).toString();
                }

                ui->table->setItem(row,10,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "politic") {//政治面貌代码表转换
                QString no=node.firstChild().toText().data();
                changepolitic.exec(QString("select Politic from PoliticCode where PoliticID='%1'").arg(no));
                while(changepolitic.next()){
                    no=changepolitic.value(0).toString();
                }
                ui->table->setItem(row,11,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "schoolmajor") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,12,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "workmajor") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,13,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "schoolname") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,14,new QTableWidgetItem(no));
            }
            else if (node.toElement().tagName() == "workname") {
                QString no=node.firstChild().toText().data();
                ui->table->setItem(row,15,new QTableWidgetItem(no));
            }
            node=node.nextSibling();
        }
        //        }
        //下一个兄弟节点,nextSiblingElement()是下一个兄弟元素
        domNode = domNode.nextSibling();
    }
    file.close();
    ui->pushButton_3->setEnabled(true);

}

void shujujieshou::on_pushButton_4_clicked()//显示选中的干部信息
{
    //    ui->iddate->clear();//清除原有数据
    //    ui->namedate->clear();
    //    ui->numdate->clear();

    ui->name->clear();
    ui->id->clear();
    ui->unit->clear();
    ui->birth->clear();
    ui->sex->clear();
    ui->zhiwu->clear();
    ui->minzu->clear();
    ui->rudang->clear();
    ui->health->clear();
    ui->jiguan->clear();
    ui->joinwork->clear();
    ui->politic->clear();
    ui->schoolmajor->clear();
    ui->workmajor->clear();
    ui->schoolname->clear();
    ui->workname->clear();
    int row = ui->table->currentRow();
    //        if(rowIndex!=-1)
    //        {
    //            ui->pushButton_2->setEnabled(true);
    //            ui->pushButton_4->setEnabled(true);
    //        }
    int i=0;
    QVariant name = ui->table->item(row,i)->text();
    QString namestring=name.toString();
    ui->name->setText(namestring);
    i++;
    QVariant birth = ui->table->item(row,i)->text();
    QString birthstring=birth.toString();
    ui->birth->setText(birthstring);
    i++;
    QVariant id = ui->table->item(row,i)->text();
    QString idstring=id.toString();
    ui->id->setText(idstring);
    i++;
    QVariant sex = ui->table->item(row,i)->text();
    QString sexstring=sex.toString();
    ui->sex->setText(sexstring);
    i++;
    QVariant unit = ui->table->item(row,i)->text();
    QString unitstring=unit.toString();
    ui->unit->setText(unitstring);
    i++;
    QVariant zhiwu = ui->table->item(row,i)->text();
    QString zhiwustring=zhiwu.toString();
    ui->zhiwu->setText(zhiwustring);
    i++;
    QVariant minzu = ui->table->item(row,i)->text();
    QString minzustring=minzu.toString();
    ui->minzu->setText(minzustring);
    i++;
    QVariant rudang = ui->table->item(row,i)->text();
    QString rudangstring=rudang.toString();
    ui->rudang->setText(rudangstring);
    i++;
    QVariant joinwork = ui->table->item(row,i)->text();
    QString joinworkstring=joinwork.toString();
    ui->joinwork->setText(joinworkstring);
    i++;
    QVariant health = ui->table->item(row,i)->text();
    QString healthworkstring=health.toString();
    ui->health->setText(healthworkstring);
    i++;
    QVariant jiguan = ui->table->item(row,i)->text();
    QString jiguanstring=jiguan.toString();
    ui->jiguan->setText(jiguanstring);
    i++;
    QVariant politic = ui->table->item(row,i)->text();
    QString politicstring=politic.toString();
    ui->politic->setText(politicstring);
    i++;
    QVariant schoolmajor = ui->table->item(row,i)->text();
    QString schoolmajorstring=schoolmajor.toString();
    ui->schoolmajor->setText(schoolmajorstring);
    i++;
    QVariant workmajor = ui->table->item(row,i)->text();
    QString workmajorstring=workmajor.toString();
    ui->workmajor->setText(workmajorstring);

    i++;
    QVariant schoolname = ui->table->item(row,i)->text();
    QString schoolnamestring=schoolname.toString();
    ui->schoolname->setText(schoolnamestring);
    i++;
    QVariant workname = ui->table->item(row,i)->text();
    QString worknamestring=workname.toString();
    ui->workname->setText(worknamestring);


}

void shujujieshou::on_pushButton_2_clicked()//将选中的干部信息导入到数据库中
{
    int judge=0;//判断条件
    int zhiwujudge=0;//职务判断
    int edujudge=0;//判断学历是否存在：在职=0，IDNum，添加在职专业major；全日=1，IDNum，添加全日专业major
    //    waityihui::judge=0;
    QSqlQuery query;
    QSqlQuery changeunit;//把单位名称换为单位的id
    QSqlQuery changeminzu;//根据民族表改变民族

    QSqlQuery selectzhiwu;//查询职务表中是否存在干部相同的职务信息,通过判断条件,限制插入语句,由于相同的数据,因此不需要更新数据
    QSqlQuery insertzhiwu;//向职务表中插入干部数据

    QSqlQuery warning;//检测到数据库中存在干部信息,输出id进行判断
    QSqlQuery changepolitic;//政治面貌改编为代码

    QSqlQuery selecteduw;//查询学历表中是否存在相同的学历
    QSqlQuery inserteduw;//向学历表中插入数据库，分为全日和在职学历：全日是1，在职是0
    QSqlQuery selectedus;//查询学历表中是否存在相同的学历
    QSqlQuery insertedus;//向学历表中插入数据库，分为全日和在职学历：全日是1，在职是0


    QSqlQuery changejiguan;
    inserteduw.prepare("INSERT INTO Edu (EduID,IDNum,Major,WorkORSchool,School)" "VALUES(0,?,?,?,?)");
    insertedus.prepare("INSERT INTO Edu (EduID,IDNum,Major,WorkORSchool,School)" "VALUES(0,?,?,?,?)");
    //    query.prepare("INSERT INTO CadreInfo(IDNum,CadreName,Phone)" "VALUES(:FaceData,:StrName,:Sijian)");
    query.prepare("INSERT INTO Ctest(IDNum,CadreName,Birth,Sex,UnitID,Zhiwu,NationID,PartyTime,JoinWorkTime,Health,NativePlace,PoliticID,SchoolEdu,WorkEdu)" "VALUES(:IDNum,:CadreName,:Birth,:Sex,:UnitID,:Zhiwu,:NationID,:PartyTime,:JoinWorkTime,:Health,:NativePlace,:PoliticID,:SchoolEdu,:WorkEdu)");
    // query.prepare("INSERT INTO Ctest(CadreName,Birth,IDNum,Sex,UnitID,NationID,PartyTime,JoinWorkTime,Health,NativePlace,Zhiwu,PoliticID,SchoolEdu,WorkEdu)" "VALUES(:CadreName,:Birth,:IDNum,:Sex,:UnitID,:NationID,:PartyTime,:JoinWorkTime,:Health,:NativePlace,:Zhiwu.:PoliticID,:SchoolEdu,:WorkEdu)");
    //    //需要先查询,选择出干部职务是否存在相同职务,若存在则更新数据,不存在则插入数据
    insertzhiwu.prepare("INSERT INTO Post(PostName,IDNum,QualifyType)""VALUES(:name,:id,:type)");
    //    insertzhiwu.prepare("INSERT INTO Post(PostID,IDNum)""VALUES(:post,:id)");
    int row=ui->table->currentRow();
    int i=0;
    QVariant name = ui->table->item(row,i)->text();
    QString namedate=name.toString();
    i++;
    QVariant birth = ui->table->item(row,i)->text();
    QString birthdate=birth.toString();
    i++;
    QVariant id = ui->table->item(row,i)->text();
    QString iddate=id.toString();
    i++;
    QVariant sex = ui->table->item(row,i)->text();
    QString sexdate=sex.toString();
    if(sexdate=="男"){
        sexdate="1";
    }else if(sexdate=="女")
    {
        sexdate="0";
    }
    i++;
    QVariant unit = ui->table->item(row,i)->text();
    QString unitdate=unit.toString();//单位需要转换成单位的id
    changeunit.exec(QString("select unitid from unit where unitname='%1'").arg(unitdate));
    while(changeunit.next()){
        unitdate=changeunit.value(0).toString();
    }
    i++;
    //************************************
    QVariant zhiwu = ui->table->item(row,i)->text();
    QString zhiwudate=zhiwu.toString();
    //    QString zhiwudate="职务";
    //******************************
    i++;
    QVariant minzu = ui->table->item(row,i)->text();//民族换为代码
    QString minzudate=minzu.toString();
    QString minzucode;
    changeminzu.exec(QString("select NationID from NationCode where Nation ='%1'").arg(minzudate));
    while(changeminzu.next()){
        minzucode=changeminzu.value(0).toString();
    }
    i++;
    QVariant rudnag = ui->table->item(row,i)->text();
    QString rudnagdate=rudnag.toString();
    i++;
    QVariant joinwork = ui->table->item(row,i)->text();
    QString joinworkdate=joinwork.toString();
    i++;
    QVariant health = ui->table->item(row,i)->text();
    QString healthdate=health.toString();
    i++;
    QVariant jiguan = ui->table->item(row,i)->text();//籍贯地点换成代码
    QString jiguandate=jiguan.toString();
    changejiguan.exec(QString("select AdminAreaID from AdminAreaCode where AdminArea='%1'").arg(jiguandate));
    while(changejiguan.next()){
        jiguandate=changejiguan.value(0).toString();
    }

    i++;
    QVariant politic = ui->table->item(row,i)->text();
    QString politicdate=politic.toString();//政治面面貌需要换成代码
    changepolitic.exec(QString("select PoliticID from PoliticCode where Politic='%1'").arg(politicdate));
    while(changepolitic.next()){
        politicdate=changepolitic.value(0).toString();
    }
    //在职是0   全日是1
    i++;
    QVariant schooledu = ui->table->item(row,i)->text();
    QString schooledudate=schooledu.toString();
    i++;
    QVariant workedu = ui->table->item(row,i)->text();
    QString workedudate=workedu.toString();

    i++;
    QVariant schoolname = ui->table->item(row,i)->text();
    QString schoolnamedate=schoolname.toString();
    i++;
    QVariant workname = ui->table->item(row,i)->text();
    QString worknamedate=workname.toString();


    //判断数据库中是否存在选中的人，跳转界面提示
    warning.exec(QString("select IDNum from Ctest where IDNum='%1'").arg(iddate));
    while(warning.next()){
        judge=1;
        jinggao=new waityihui;//定义一个新界面
        connect(this,SIGNAL(sendData(QString,QString)),jinggao,SLOT(recvData(QString,QString)));
        emit sendData(ui->table->item(row,0)->text(),iddate);//发送lineEdit中的文本，sendData信号
        jinggao->show();
        //QString name= warning.value(0).toString();
    }
    if(judge==0){
        query.bindValue(":IDNum",iddate);//this->ui->iddate->text()
        query.bindValue(":CadreName",namedate);//this->ui->iddate->text()
        query.bindValue(":Birth",birthdate);//this->ui->iddate->text()
        query.bindValue(":Sex",sexdate);//this->ui->iddate->text()
        query.bindValue(":UnitID",unitdate);//this->ui->iddate->text()
        query.bindValue(":Zhiwu",zhiwudate);//this->ui->iddate->text()
        query.bindValue(":NationID",minzucode);//this->ui->iddate->text()
        query.bindValue(":PartyTime",rudnagdate);//this->ui->iddate->text()
        query.bindValue(":JoinWorkTime",joinworkdate);//this->ui->iddate->text()
        query.bindValue(":Health",healthdate);//this->ui->iddate->text()
        query.bindValue(":NativePlace",jiguandate);//this->ui->iddate->text()
        query.bindValue(":PoliticID",politicdate);//this->ui->iddate->text()
        query.bindValue(":SchoolEdu",schooledudate);//this->ui->iddate->text()
        query.bindValue(":WorkEdu",workedudate);//this->ui->iddate->text()

        selectzhiwu.exec(QString("select PostID from Post where PostName='%1' and IDNum='%2' and QualifyType='%3'").arg(zhiwudate).arg(iddate).arg(0));
        while(selectzhiwu.next())
        {
            zhiwujudge=1;
            qDebug()<<"系统中有这个干部的职务信息";
            QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的职务信息"));
            //QString postid=selectzhiwu.value(0).toString();
            //qDebug()<<postid;
        }
        if(zhiwujudge==0){
            //insertzhiwu.bindValue(":post",1);//this->ui->iddate->text()
            insertzhiwu.bindValue(":name",zhiwudate);//this->ui->iddate->text()
            insertzhiwu.bindValue(":id",iddate);//this->ui->iddate->text()
            insertzhiwu.bindValue(":type",0);//this->ui->iddate->text()//0现任职务
            //    query.bindValue(":ata",12);//this->ui->iddate->text()//一定要有冒号：
            //    query.bindValue(":StrName",jiguanstring);
            //    query.bindValue(":Sijian",this->ui->dateEdit->text());
            //    query.bindValue(":FaceData",11);//this->ui->iddate->text()
            //    query.bindValue(":StrName",this->ui->namedate->text());
            //    query.bindValue(":Sijian",this->ui->numdate->text());
            insertzhiwu.exec();
        }
        //在职=0
        selecteduw.exec(QString("select EduID from Edu where IDNum='%1'and Major='%2'and WorkORSchool='%3' and School='%4'").arg(iddate).arg(workedudate).arg(0).arg(worknamedate));
        while(selecteduw.next()){
            edujudge=1;

            QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的在职院校以及专业信息"));
        }
        if(edujudge==0){
            //            insertedu.prepare("insert into Edu(Major,WorkORSchool,IDNum) values(:Major,:WorkORSchool,:IDNum)");

            inserteduw.bindValue(0,iddate);
            inserteduw.bindValue(1,workedudate);
            inserteduw.bindValue(2,0);
            inserteduw.bindValue(3,worknamedate);
            inserteduw.exec();
        }
        edujudge=0;
        //全日=1
        selectedus.exec(QString("select EduID from Edu where IDNum='%1'and Major='%2'and WorkORSchool='%3' and School='%4'").arg(iddate).arg(schooledudate).arg(1).arg(schoolnamedate));
        while(selectedus.next()){
            edujudge=1;

            QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的全日院校以及专业信息"));
        }
        if(edujudge==0){
            //            insertedu.prepare("insert into Edu(Major,WorkORSchool,IDNum) values(:Major,:WorkORSchool,:IDNum)");

            insertedus.bindValue(0,iddate);
            insertedus.bindValue(1,schooledudate);
            insertedus.bindValue(2,1);
             insertedus.bindValue(3,schoolnamedate);
            insertedus.exec();
        }
        query.exec();
    }
}

void shujujieshou::on_pushButton_3_clicked()//添加全部干部信息
{
    int judge=0;//判断条件
    int zhiwujudge=0;//职务判断
    int edujudge=0;//判断学历是否存在：在职=0，IDNum，添加在职专业major；全日=1，IDNum，添加全日专业major
    //    waityihui::judge=0;
    QSqlQuery query;
    QSqlQuery changeunit;//把单位名称换为单位的id
    QSqlQuery changeminzu;//根据民族表改变民族

    QSqlQuery selectzhiwu;//查询职务表中是否存在干部相同的职务信息,通过判断条件,限制插入语句,由于相同的数据,因此不需要更新数据
    QSqlQuery insertzhiwu;//向职务表中插入干部数据

    QSqlQuery warning;//检测到数据库中存在干部信息,输出id进行判断
    QSqlQuery changepolitic;//政治面貌改编为代码
    QSqlQuery changejiguan;
    QSqlQuery selecteduw;//查询学历表中是否存在相同的学历
    QSqlQuery inserteduw;//向学历表中插入数据库，分为全日和在职学历：全日是1，在职是0
    QSqlQuery selectedus;//查询学历表中是否存在相同的学历
    QSqlQuery insertedus;//向学历表中插入数据库，分为全日和在职学历：全日是1，在职是0
    inserteduw.prepare("INSERT INTO Edu (EduID,IDNum,Major,WorkORSchool,School)" "VALUES(0,?,?,?,?)");
    insertedus.prepare("INSERT INTO Edu (EduID,IDNum,Major,WorkORSchool,School)" "VALUES(0,?,?,?,?)");
    //    query.prepare("INSERT INTO CadreInfo(IDNum,CadreName,Phone)" "VALUES(:FaceData,:StrName,:Sijian)");
    query.prepare("INSERT INTO Ctest(IDNum,CadreName,Birth,Sex,UnitID,Zhiwu,NationID,PartyTime,JoinWorkTime,Health,NativePlace,PoliticID,SchoolEdu,WorkEdu)" "VALUES(:IDNum,:CadreName,:Birth,:Sex,:UnitID,:Zhiwu,:NationID,:PartyTime,:JoinWorkTime,:Health,:NativePlace,:PoliticID,:SchoolEdu,:WorkEdu)");
    // query.prepare("INSERT INTO Ctest(CadreName,Birth,IDNum,Sex,UnitID,NationID,PartyTime,JoinWorkTime,Health,NativePlace,Zhiwu,PoliticID,SchoolEdu,WorkEdu)" "VALUES(:CadreName,:Birth,:IDNum,:Sex,:UnitID,:NationID,:PartyTime,:JoinWorkTime,:Health,:NativePlace,:Zhiwu.:PoliticID,:SchoolEdu,:WorkEdu)");
    //    //需要先查询,选择出干部职务是否存在相同职务,若存在则更新数据,不存在则插入数据
    insertzhiwu.prepare("INSERT INTO Post(PostName,IDNum,QualifyType)""VALUES(:name,:id,:type)");
    //    insertzhiwu.prepare("INSERT INTO Post(PostID,IDNum)""VALUES(:post,:id)");

    int rowCount=ui->table->rowCount();
    for(int row=0;row<rowCount;row++){

        int i=0;
        QVariant name = ui->table->item(row,i)->text();
        QString namedate=name.toString();
        i++;
        QVariant birth = ui->table->item(row,i)->text();
        QString birthdate=birth.toString();
        i++;
        QVariant id = ui->table->item(row,i)->text();
        QString iddate=id.toString();
        i++;
        QVariant sex = ui->table->item(row,i)->text();
        QString sexdate=sex.toString();
        if(sexdate=="男"){
            sexdate="1";
        }else if(sexdate=="女")
        {
            sexdate="0";
        }
        i++;
        QVariant unit = ui->table->item(row,i)->text();
        QString unitdate=unit.toString();//单位需要转换成单位的id
        changeunit.exec(QString("select unitid from unit where unitname='%1'").arg(unitdate));
        while(changeunit.next()){
            unitdate=changeunit.value(0).toString();
        }
        i++;
        //************************************
        QVariant zhiwu = ui->table->item(row,i)->text();
        QString zhiwudate=zhiwu.toString();
        //    QString zhiwudate="职务";
        //******************************
        i++;
        QVariant minzu = ui->table->item(row,i)->text();//民族换为代码
        QString minzudate=minzu.toString();
        QString minzucode;
        changeminzu.exec(QString("select NationID from NationCode where Nation ='%1'").arg(minzudate));
        while(changeminzu.next()){
            minzucode=changeminzu.value(0).toString();
        }
        i++;
        QVariant rudnag = ui->table->item(row,i)->text();
        QString rudnagdate=rudnag.toString();
        i++;
        QVariant joinwork = ui->table->item(row,i)->text();
        QString joinworkdate=joinwork.toString();
        i++;
        QVariant health = ui->table->item(row,i)->text();
        QString healthdate=health.toString();
        i++;
        QVariant jiguan = ui->table->item(row,i)->text();
        QString jiguandate=jiguan.toString();
        changejiguan.exec(QString("select AdminAreaID from AdminAreaCode where AdminArea='%1'").arg(jiguandate));
        while(changejiguan.next()){
            jiguandate=changejiguan.value(0).toString();
        }
        i++;
        QVariant politic = ui->table->item(row,i)->text();
        QString politicdate=politic.toString();//政治面面貌需要换成代码
        changepolitic.exec(QString("select PoliticID from PoliticCode where Politic='%1'").arg(politicdate));
        while(changepolitic.next()){
            politicdate=changepolitic.value(0).toString();
        }
        //在职是0   全日是1
        i++;
        QVariant schooledu = ui->table->item(row,i)->text();
        QString schooledudate=schooledu.toString();//在职专业需要插入学历表
        i++;
        QVariant workedu = ui->table->item(row,i)->text();
        QString workedudate=workedu.toString();//全日专业需要插入学历表

        i++;
        QVariant schoolname = ui->table->item(row,i)->text();
        QString schoolnamedate=schoolname.toString();
        i++;
        QVariant workname = ui->table->item(row,i)->text();
        QString worknamedate=workname.toString();
        //判断数据库中是否存在选中的人，跳转界面提示
        warning.exec(QString("select IDNum from Ctest where IDNum='%1'").arg(iddate));
        while(warning.next()){
            judge=1;
            jinggao=new waityihui;//定义一个新界面
            connect(this,SIGNAL(sendData(QString,QString)),jinggao,SLOT(recvData(QString,QString)));
            emit sendData(ui->table->item(row,0)->text(),iddate);//发送lineEdit中的文本，sendData信号
            jinggao->show();
            //QString name= warning.value(0).toString();
        }
        if(judge==0){
            query.bindValue(":IDNum",iddate);//this->ui->iddate->text()
            query.bindValue(":CadreName",namedate);//this->ui->iddate->text()
            query.bindValue(":Birth",birthdate);//this->ui->iddate->text()
            query.bindValue(":Sex",sexdate);//this->ui->iddate->text()
            query.bindValue(":UnitID",unitdate);//this->ui->iddate->text()
            query.bindValue(":Zhiwu",zhiwudate);//this->ui->iddate->text()
            query.bindValue(":NationID",minzucode);//this->ui->iddate->text()
            query.bindValue(":PartyTime",rudnagdate);//this->ui->iddate->text()
            query.bindValue(":JoinWorkTime",joinworkdate);//this->ui->iddate->text()
            query.bindValue(":Health",healthdate);//this->ui->iddate->text()
            query.bindValue(":NativePlace",jiguandate);//this->ui->iddate->text()
            query.bindValue(":PoliticID",politicdate);//this->ui->iddate->text()
            query.bindValue(":SchoolEdu",schooledudate);//this->ui->iddate->text()
            query.bindValue(":WorkEdu",workedudate);//this->ui->iddate->text()

            selectzhiwu.exec(QString("select PostID from Post where PostName='%1' and IDNum='%2' and QualifyType='%3'").arg(zhiwudate).arg(iddate).arg(0));
            while(selectzhiwu.next())
            {
                zhiwujudge=1;
                qDebug()<<"系统中有这个干部的职务信息";
                QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的职务信息"));
                //QString postid=selectzhiwu.value(0).toString();
                //qDebug()<<postid;
            }
            if(zhiwujudge==0){
                //insertzhiwu.bindValue(":post",1);//this->ui->iddate->text()
                insertzhiwu.bindValue(":name",zhiwudate);//this->ui->iddate->text()
                insertzhiwu.bindValue(":id",iddate);//this->ui->iddate->text()
                insertzhiwu.bindValue(":type",0);//this->ui->iddate->text()//0现任职务
                //    query.bindValue(":ata",12);//this->ui->iddate->text()//一定要有冒号：
                //    query.bindValue(":StrName",jiguanstring);
                //    query.bindValue(":Sijian",this->ui->dateEdit->text());
                //    query.bindValue(":FaceData",11);//this->ui->iddate->text()
                //    query.bindValue(":StrName",this->ui->namedate->text());
                //    query.bindValue(":Sijian",this->ui->numdate->text());
                insertzhiwu.exec();
            }
            //在职=0
            selecteduw.exec(QString("select EduID from Edu where IDNum='%1'and Major='%2'and WorkORSchool='%3' and School='%4'").arg(iddate).arg(workedudate).arg(0).arg(worknamedate));
            while(selecteduw.next()){
                edujudge=1;
                qDebug()<<"存在在职专业";
                QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的在职专业信息"));
            }
            if(edujudge==0){
                //            insertedu.prepare("insert into Edu(Major,WorkORSchool,IDNum) values(:Major,:WorkORSchool,:IDNum)");
                qDebug()<<"存入在职专业";
                inserteduw.bindValue(0,iddate);
                inserteduw.bindValue(1,workedudate);
                inserteduw.bindValue(2,0);
                 inserteduw.bindValue(3,worknamedate);
                inserteduw.exec();
            }
            edujudge=0;
            //全日=1
            selectedus.exec(QString("select EduID from Edu where IDNum='%1'and Major='%2'and WorkORSchool='%3' and School='%4'").arg(iddate).arg(schooledudate).arg(1).arg(schoolnamedate));
            while(selectedus.next()){
                edujudge=1;
                qDebug()<<"存在全日专业";
                QMessageBox::information(this, tr("提示信息"), tr("系统中有")+namedate+("的全日专业信息"));
            }
            if(edujudge==0){
                //            insertedu.prepare("insert into Edu(Major,WorkORSchool,IDNum) values(:Major,:WorkORSchool,:IDNum)");
                qDebug()<<"存入全日专业";
                insertedus.bindValue(0,iddate);
                insertedus.bindValue(1,schooledudate);
                insertedus.bindValue(2,1);
                insertedus.bindValue(3,schoolnamedate);
                insertedus.exec();
            }
            query.exec();
        }
    }
}

//void shujujieshou::pressBtn()//选择干部按钮连接
//{
//    int row=ui->table->currentRow();//选择的行
//    QSqlQuery warning;
//    // 判断数据库中是否存在选中的人，跳转界面提示

//    QVariant id = ui->table->item(row,2)->text();
//    QString iddate=id.toString();
//    warning.exec(QString("select IDNum from Ctest where IDNum='%1'").arg(iddate));
//            while(warning.next()){
//                emit sendData(ui->table->item(row,0)->text());//发送lineEdit中的文本，sendData信号
//                jinggao=new waityihui;
//                jinggao->show();
//            }
//}

void shujujieshou::on_table_clicked()//解锁按钮
{
    ui->pushButton_2->setEnabled(true);
    ui->pushButton_4->setEnabled(true);
    ui->pushButton_6->setEnabled(true);

}

void shujujieshou::on_pushButton_5_clicked()//数据统计查找按钮
{


    int rowNum =  ui->choose->rowCount();
    for(int i = 0;i < rowNum;i++)
    {
        ui->choose->removeRow(0);
    }



    QString name;
    QString id;
    bool namenotempty=!ui->nameselect->text().isEmpty();
    bool idnotempty=!ui->idselect->text().isEmpty();
    QSqlQuery select;
    QSqlQuery selectunit;
    QSqlQuery selectjiguan;
    QSqlQuery selectworkeduname;
    QSqlQuery selectschooleduname;
    QString all;
    //0 id 1 name 2 unit 3 sex
    all=QString("select IDNum,CadreName,UnitID,Sex,NativePlace from CadreInfo where  ");
    if(namenotempty){
        name=QString(" CadreName='%1' ").arg(ui->nameselect->text());
        if(idnotempty){
            id=QString(" and IDNum='%1' ").arg(ui->idselect->text());
            all=all+name+id;
        }else if(!idnotempty){
            all=all+name;
        }
    }else if(!namenotempty){
        if(idnotempty){
            id=QString(" IDNum='%1' ").arg(ui->idselect->text());
            all=all+id;
        }else if(!idnotempty){
            all="NULL";
            QMessageBox::information(this, tr("提示信息"), tr("请输入干部信息"));
        }
    }


    select.exec(all);
    while(select.next()){
        int rowcount=ui->choose->rowCount();//有几列
        ui->choose->insertRow(rowcount);//加一行table

        QString idnum=select.value(0).toString();
        ui->choose->setItem(rowcount,1,new QTableWidgetItem(idnum));

        QString name=select.value(1).toString();
        ui->choose->setItem(rowcount,0,new QTableWidgetItem(name));

        QString unitname;

        QString unit=select.value(2).toString();
        selectunit.exec(QString("select unitname from unit where unitid='%1'").arg(unit));
        while(selectunit.next()){
            unitname=selectunit.value(0).toString();
        }
        ui->choose->setItem(rowcount,2,new QTableWidgetItem(unitname));

        QString sex=select.value(3).toString();
        if(sex=="1"){
            sex="男";
        }else if(sex=="0")
        {
            sex="女";
        }
        ui->choose->setItem(rowcount,3,new QTableWidgetItem(sex));

        QString jiguan=select.value(4).toString();
        selectjiguan.exec(QString("select AdminArea from AdminAreaCode where AdminAreaID='%1'").arg(jiguan));
        while(selectjiguan.next()){
            jiguan=selectjiguan.value(0).toString();
        }
        ui->choose->setItem(rowcount,4,new QTableWidgetItem(jiguan));

        QString workeduname;//0
        QString schooleduname;//1
        selectworkeduname.exec(QString("select School from Edu where IDNum='%1' and WorkORSchool=0").arg(idnum));
        while(selectworkeduname.next()){
            workeduname=selectworkeduname.value(0).toString();
        }
        selectschooleduname.exec(QString("select School from Edu where IDNum='%1' and WorkORSchool=1").arg(idnum));
        while(selectschooleduname.next()){
            schooleduname=selectschooleduname.value(0).toString();
        }
        ui->choose->setItem(rowcount,5,new QTableWidgetItem(workeduname));
        ui->choose->setItem(rowcount,6,new QTableWidgetItem(schooleduname));
    }
}

void shujujieshou::on_choose_doubleClicked()//双击choose选择一个干部
{

    int rowNum11 =  ui->sameplace->rowCount();
    for(int i = 0;i < rowNum11;i++)
    {
        ui->sameplace->removeRow(0);
    }

    int rowNum1 =  ui->sameworkedu->rowCount();
    for(int i = 0;i < rowNum1;i++)
    {
        ui->sameworkedu->removeRow(0);
    }

    int rowNum3 =  ui->sameschooledu->rowCount();
    for(int i = 0;i < rowNum3;i++)
    {
        ui->sameschooledu->removeRow(0);
    }
    int select=ui->choose->currentIndex().row();
    //0 id 1 name 2 unit 3 sex
    QVariant var = ui->choose->item(select,1)->text();
    QString id=var.toString();
    QSqlQuery selectsamejiguan;
    QSqlQuery selectsameschooledu;
    QSqlQuery selectsameworkedu;
    QSqlQuery selectunit;
    QSqlQuery selectjiguan;
    QSqlQuery all;
    QSqlQuery selectworkeduname;
    QSqlQuery selectschooleduname;
    QString unit;
    QString jiguan;
    QString workeduname;
    QString schooleduname;

    all.exec(QString("select UnitID,NativePlace from CadreInfo where IDNum='%1'").arg(id));
    while(all.next()){
        unit=all.value(0).toString();
        jiguan=all.value(1).toString();

        selectworkeduname.exec(QString("select School from Edu where IDNum='%1' and WorkORSchool=0").arg(id));
        while(selectworkeduname.next()){
            workeduname=selectworkeduname.value(0).toString();
        }
        selectschooleduname.exec(QString("select School from Edu where IDNum='%1' and WorkORSchool=1").arg(id));
        while(selectschooleduname.next()){
            schooleduname=selectschooleduname.value(0).toString();
        }
    }

    selectsamejiguan.exec(QString("select IDNum,CadreName,UnitID,Sex,NativePlace from CadreInfo where NativePlace='%1' and UnitID='%2'").arg(jiguan).arg(unit));
    while(selectsamejiguan.next()){

        int rowcount=ui->sameplace->rowCount();//有几列
        ui->sameplace->insertRow(rowcount);//加一行table

        QString idnum=selectsamejiguan.value(0).toString();
        ui->sameplace->setItem(rowcount,1,new QTableWidgetItem(idnum));

        QString name=selectsamejiguan.value(1).toString();
        ui->sameplace->setItem(rowcount,0,new QTableWidgetItem(name));

        QString unitname;

        QString unit=selectsamejiguan.value(2).toString();
        selectunit.exec(QString("select unitname from unit where unitid='%1'").arg(unit));
        while(selectunit.next()){
            unitname=selectunit.value(0).toString();
        }
        ui->sameplace->setItem(rowcount,2,new QTableWidgetItem(unitname));

        QString sex=selectsamejiguan.value(3).toString();
        if(sex=="1"){
            sex="男";
        }else if(sex=="0")
        {
            sex="女";
        }
        ui->sameplace->setItem(rowcount,3,new QTableWidgetItem(sex));

        QString jiguan=selectsamejiguan.value(4).toString();
        selectjiguan.exec(QString("select AdminArea from AdminAreaCode where AdminAreaID='%1'").arg(jiguan));
        while(selectjiguan.next()){
            jiguan=selectjiguan.value(0).toString();
        }
        ui->sameplace->setItem(rowcount,4,new QTableWidgetItem(jiguan));

        if(id==idnum){
            ui->sameplace->removeRow(rowcount);
        }
    }

        selectsameworkedu.exec(QString("select c.CadreName,c.IDNum,c.UnitID,c.Sex,e.School from Edu e,CadreInfo c where c.IDNum=e.IDNum and e.School='%1' and e.WorkORSchool=0 and c.UnitID='%2'").arg(workeduname).arg(unit));
        while(selectsameworkedu.next()){

            int rowcount=ui->sameworkedu->rowCount();//有几列
            ui->sameworkedu->insertRow(rowcount);//加一行table

            QString name=selectsameworkedu.value(0).toString();
            ui->sameworkedu->setItem(rowcount,0,new QTableWidgetItem(name));

            QString idnum=selectsameworkedu.value(1).toString();
            ui->sameworkedu->setItem(rowcount,1,new QTableWidgetItem(idnum));

            QString unitname;

            QString unit=selectsameworkedu.value(2).toString();
            selectunit.exec(QString("select unitname from unit where unitid='%1'").arg(unit));
            while(selectunit.next()){
                unitname=selectunit.value(0).toString();
            }
            ui->sameworkedu->setItem(rowcount,2,new QTableWidgetItem(unitname));

            QString sex=selectsameworkedu.value(3).toString();
            if(sex=="1"){
                sex="男";
            }else if(sex=="0")
            {
                sex="女";
            }
            ui->sameworkedu->setItem(rowcount,3,new QTableWidgetItem(sex));

            QString school=selectsameworkedu.value(4).toString();
            ui->sameworkedu->setItem(rowcount,4,new QTableWidgetItem(school));


            if(id==idnum){
                ui->sameworkedu->removeRow(rowcount);
            }
        }

        selectsameschooledu.exec(QString("select c.CadreName,c.IDNum,c.UnitID,c.Sex,e.School from Edu e,CadreInfo c where c.IDNum=e.IDNum and e.School='%1' and e.WorkORSchool=1 and c.UnitID='%2'").arg(schooleduname).arg(unit));
        while(selectsameschooledu.next()){

            int rowcount=ui->sameschooledu->rowCount();//有几列
            ui->sameschooledu->insertRow(rowcount);//加一行table

            QString name=selectsameschooledu.value(0).toString();
            ui->sameschooledu->setItem(rowcount,0,new QTableWidgetItem(name));

            QString idnum=selectsameschooledu.value(1).toString();
            ui->sameschooledu->setItem(rowcount,1,new QTableWidgetItem(idnum));

            QString unitname;

            QString unit=selectsameschooledu.value(2).toString();
            selectunit.exec(QString("select unitname from unit where unitid='%1'").arg(unit));
            while(selectunit.next()){
                unitname=selectunit.value(0).toString();
            }
            ui->sameschooledu->setItem(rowcount,2,new QTableWidgetItem(unitname));

            QString sex=selectsameschooledu.value(3).toString();
            if(sex=="1"){
                sex="男";
            }else if(sex=="0")
            {
                sex="女";
            }
            ui->sameschooledu->setItem(rowcount,3,new QTableWidgetItem(sex));

        QString school=selectsameschooledu.value(4).toString();
        ui->sameschooledu->setItem(rowcount,4,new QTableWidgetItem(school));
            if(id==idnum){
                ui->sameschooledu->removeRow(rowcount);
            }
        }

    int sameplacenum1=ui->sameplace->rowCount();
    QString placenum1 = QString::number(sameplacenum1);
    ui->sameplacenum->setText(placenum1);

    int sameschooledunum1=ui->sameschooledu->rowCount();
    QString schooledunum1 = QString::number(sameschooledunum1);
    ui->sameschooledunum->setText(schooledunum1);

    int sameworkedunum1=ui->sameworkedu->rowCount();
    QString workedunum1 = QString::number(sameworkedunum1);
    ui->sameworkedunum->setText(workedunum1);

    if(placenum1=="0"){
        QMessageBox::information(this, tr("提示信息"), tr("在单位中没有与选中干部籍贯相同的干部"));
    }
    if(schooledunum1=="0"){
        QMessageBox::information(this, tr("提示信息"), tr("在单位中没有与选中干部全日制学校相同的干部"));
    }
    if(workedunum1=="0"){
        QMessageBox::information(this, tr("提示信息"), tr("在单位中没有与选中干部在职学校相同的干部"));
    }

}

void shujujieshou::on_pushButton_6_clicked()//查看选择的数据统计
{
    int row = ui-> table ->currentIndex().row();
    QString name=ui->table->item(row,0)->text();
    QString id=ui->table->item(row,2)->text();
    QString unit=ui->table->item(row,4)->text();
    QString sex=ui->table->item(row,3)->text();
    QString jiguan=ui->table->item(row,10)->text();
    QString workname=ui->table->item(row,15)->text();
    QString schoolname=ui->table->item(row,14)->text();
    int rowcount=ui->choose->rowCount();
    ui->choose->insertRow(rowcount);
    ui->choose->setItem(rowcount,0,new QTableWidgetItem(name));
    ui->choose->setItem(rowcount,1,new QTableWidgetItem(id));
    ui->choose->setItem(rowcount,2,new QTableWidgetItem(unit));
    ui->choose->setItem(rowcount,3,new QTableWidgetItem(sex));
    ui->choose->setItem(rowcount,4,new QTableWidgetItem(jiguan));
    ui->choose->setItem(rowcount,5,new QTableWidgetItem(workname));
    ui->choose->setItem(rowcount,6,new QTableWidgetItem(schoolname));
}
